Entity Relationships - Maytas Hub Communities (COMMUNITY)

Related Tables

Table Name	Table Description	Primary Keys	Usually Joins From	Join Type	Joins On	Details
COMMUNITY	Community	COMMUNITYID				Stores basic details of each community.
COMMUNITYCOURSES	Community Courses	COMMUNITYCOURSEID	COMMUNITY	One to many	COMMUNITYID	Stores list of courses linked to each community.
COMMUNITYFILES	Community Files	FILEID	COMMUNITY	One to many	COMMUNITYID	Stores list of files linked to each community.
COMMUNITYFORUM	Community Forum	COMMUNITYFORUMID	COMMUNITY	One to many	COMMUNITYID	List of forum and group IDs for each community
COMMUNITYFORUMEVIDENCE	Community Forum Evidence	TRAINEEID, FILEID	COMMUNITYTOPIC	One to many	COMMUNITYTOPICID	List of evidence files for each community topic.
COMMUNITYGROUP	Community Group	COMMUNITYGROUPID	COMMUNITY	One to many	COMMUNITYID	List of groups for each community.
COMMUNITYMODULES	Community Modules	COMMUNITYMODULEID	COMMUNITYGROUP	One to many	COMMUNITYGROUPID	List of objectives assigned to each group.
COMMUNITYPOST	Community topic post	COMMUNITYPOSTID, COMMUNITYTOPICID	COMMUNITYTOPIC	One to many	COMMUNITYTOPICID	Details of posts in community topics.
COMMUNITYPOSTLIKES	Community post likes	COMMUNITYPOSTID, USERNAME	COMMUNITYPOST	One to many	COMMUNITYPOSTID	Likes for each post in a topic, by user.
COMMUNITYRESOURCE	Community Resource	COMMUNITYRESOURCEID	COMMUNITY	One to many	COMMUNITYID	Details of resources assigned to each community.
COMMUNITYTASK	Community Task	COMMUNITYTASKID	COMMUNITYRESOURCE	One to many	COMMUNITYRESOURCEID	Details of tasks assigned for each resource.
COMMUNITYTASKPREREQS	Community Task Pre-Requisites	PREREQTASKID	COMMUNITYTASK	One to many	COMMUNITYTASKID	List of pre-requisites for each task.
COMMUNITYTOPIC	Community Topic	COMMUNITYTOPICID, COMMUNITYFORUMID	COMMUNITYFORUM	One to many	COMMUNITYFORUMID	Details of topics in each forum.
COMMUNITYTOPICUSERS	Community Topic Users	COMMUNITYTOPICID, USERNAME	COMMUNITYTOPIC	Many to many	COMMUNITYTOPICID	List of users per topic.
COMMUNITYUSER	Community User	COMMUNITYID, USERNAME	COMMUNITY	Many to many	COMMUNITYID	Details of users per community.
COMMUNITYUSERGROUP	Community User Group	COMMUNITYGROUPID, USERNAME, COMMUNITYID	COMMUNITYGROUP	One to many	COMMUNITYGROUPID	List of users for each group.
COMMUNITYUSERTASK	Community User Task	COMMUNITYUSERTASKID	COMMUNITYTASK	Many to many	COMMUNITYTASKID	Intermediary between tasks and users.
LRS_STATEMENT	Resource Tracking Statement	ID	COMMUNITYRESOURCE	One to one	See details	Main details for tracking resource completion by community users. The join from COMMUNITYRESOURCE is complex as it checks LRS_STATEMENT.ID against IDs stored in COMMUNITYRESOURCE.MANIFEST (i.e. there is not a simple comparison between the two column values).
LRS_ACTIVITY	Resource Tracking Actions	ID				List of actions taken by a user regarding resource tracking.
LRS_ACTIVITYCONTEXT	Resource Tracking Activity Context	ACTIVITYID	LRS_ACTIVITY	One to many	ID = ACTIVITYID	Tracks the statements made for each action by a user. Also joins one-to-one with LRS_STATEMENT on LRS_STATEMENT.ID = LRS_ACTIVITYCONTEXT.STATEMENTID
LRS_ACTOR	Resource Tracking Actor	ID	LRS_STATEMENT	One to one	ACTOR = ID	Lists the user associated with a statement.
LRS_STATE	Resource Tracking State	ID	LRS_ACTOR	One to many	ID = ACTOR	Lists the states of an activity for each actor (e.g. if a user quits halfway through an action, a 'RESUME' state would be created).
LRS_VERB	Resource Tracking Verb	ID	LRS_STATEMENT	One to one	VERB = ID	Lookup for verbs used in statements.

Many of the community tables can be joined directly back to COMMUNITY using COMMUNITYID, although only several of these are shown on the diagram.

A high-level explanation of the statement design behind the LRS tables can be found here.